﻿Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Partial Class nFileKT
    Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub

    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region
    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
        If Not Page.IsPostBack Then

        End If
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If Not Page.IsValid Then Exit Sub
        On Error GoTo hErr
        'Open a file for reading
        Dim strName, strCode As String
        Dim iIndex, iCount As Int16
        iCount = 0
        strName = txtName.Text
        Dim FILENAME As String = Server.MapPath("upload\" & strName.ToString)
        'Get a StreamReader class that can be used to read the file
        Dim objStreamReader As StreamReader
        objStreamReader = File.OpenText(FILENAME)

        'Now, read the entire file into a string
        'Dim contents As String = objStreamReader.ReadToEnd()
        Dim sString As String
        Dim strDataFields() As String
        Dim i, intMin, iUpdate, iInsert As Integer
        i = 0 : iUpdate = 0 : iInsert = 0
        Dim sSql As String
        Dim myConn As New SqlConnection(ConfigurationManager.AppSettings("cons"))
        Dim MyCommand As SqlCommand
        myConn.Open()
        MyCommand = New SqlCommand("select 1 from kichthuoc", myConn)
        While objStreamReader.Peek() <> -1
            sString = objStreamReader.ReadLine()
            sString = remove_SpeKey(sString)
            If Trim(sString) <> "" Then
                If i > 0 Then
                    ' Data 
                    'On Error Resume Next
                    strDataFields = Split(sString, ",")
                    If strDataFields(0) <> "" And strDataFields(1) <> "" Then
                        sSql = "select count(1) from kichthuoc where makt='" + strDataFields(0).ToString + "' "
                        MyCommand = New SqlCommand(sSql, myConn)
                        If MyCommand.ExecuteScalar > 0 Then
                            sSql = "update kichthuoc set tensp=@tensp,ban=@ban,s1=@s1,s2=@s2,s3=@s3,s4=@s4,s5=@s5,s6=@s6 where makt=@makt"
                            iUpdate += 1
                        Else
                            sSql = "insert into kichthuoc(makt,tensp,ban, s1,s2,s3,s4,s5,s6) values(@makt,@tensp,@ban,@s1,@s2,@s3,@s4,@s5,@s6)"
                            iInsert += 1
                        End If
                        On Error GoTo SQLError
                        MyCommand.CommandText = sSql
                        MyCommand.Parameters.Add(New SqlParameter("@makt", strDataFields(0)))
                        MyCommand.Parameters.Add(New SqlParameter("@tensp", strDataFields(1)))
                        MyCommand.Parameters.Add(New SqlParameter("@ban", strDataFields(2)))
                        MyCommand.Parameters.Add(New SqlParameter("@s1", strDataFields(3)))
                        MyCommand.Parameters.Add(New SqlParameter("@s2", strDataFields(4)))
                        MyCommand.Parameters.Add(New SqlParameter("@s3", strDataFields(5)))
                        MyCommand.Parameters.Add(New SqlParameter("@s4", IIf(strDataFields(6) = "", DBNull.Value, strDataFields(6))))
                        MyCommand.Parameters.Add(New SqlParameter("@s5", IIf(strDataFields(7) = "", DBNull.Value, strDataFields(7))))
                        MyCommand.Parameters.Add(New SqlParameter("@s6", IIf(strDataFields(8) = "", DBNull.Value, strDataFields(8))))
                        MyCommand.ExecuteNonQuery()
                        MyCommand.Dispose()
                        iCount += 1
SQLError:
                    End If
                End If
                i = i + 1
            End If
        End While
        objStreamReader.Close()
        Label1.Text = iCount.ToString + "::" + " Update: " + iUpdate.ToString + " Insert: " + iInsert.ToString
        MyCommand.Dispose()
        myConn.Close()
        Exit Sub
hErr:
        objStreamReader.Close()
        MyCommand.Dispose()
        myConn.Close()
    End Sub

    Private Function remove_SpeKey(ByVal vString As String) As String
        Dim strString As String
        strString = Replace(vString, """", "")
        Return strString
    End Function

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        If Not Page.IsValid Then Exit Sub
        On Error GoTo hErr
        'Open a file for reading
        Dim strName, strCode As String
        Dim iIndex, iCount As Int16
        iCount = 0
        strName = txtName.Text
        Dim FILENAME As String = Server.MapPath("upload\" & strName.ToString)
        'Get a StreamReader class that can be used to read the file
        Dim objStreamReader As StreamReader
        objStreamReader = File.OpenText(FILENAME)

        'Now, read the entire file into a string
        'Dim contents As String = objStreamReader.ReadToEnd()
        Dim sString As String
        Dim strDataFields() As String
        Dim i, intMin, iUpdate, iInsert As Integer
        i = 0 : iUpdate = 0 : iInsert = 0
        Dim sSql As String = Nothing
        Dim myConn As New SqlConnection(ConfigurationManager.AppSettings("cons"))
        Dim MyCommand As SqlCommand
        myConn.Open()
        MyCommand = New SqlCommand("select 1 from khachsan", myConn)
        While objStreamReader.Peek() <> -1
            sString = objStreamReader.ReadLine()
            sString = remove_SpeKey(sString)
            If Trim(sString) <> "" Then
                If i > 0 Then
                    ' Data 
                    'On Error Resume Next
                    strDataFields = Split(sString, ";")
                    If strDataFields(0) <> "" And strDataFields(1) <> "" Then
                        MyCommand = New SqlCommand(sSql, myConn)
                        sSql = "insert into khachsan(tenks,diachi,quan_huyen,tinh_tp, dienthoai,hangsao,resort,tinh_tp1,web,chuatrangbi) values(@tenks,@diachi,@quan_huyen,@tinh_tp, @dienthoai,@hangsao,@resort,@tinh_tp1,@web,@chuatrangbi)"
                        iInsert += 1

                        On Error GoTo SQLError
                        MyCommand.CommandText = sSql
                        MyCommand.Parameters.Add(New SqlParameter("@tenks", strDataFields(0)))
                        MyCommand.Parameters.Add(New SqlParameter("@diachi", strDataFields(1)))
                        MyCommand.Parameters.Add(New SqlParameter("@quan_huyen", strDataFields(2)))
                        MyCommand.Parameters.Add(New SqlParameter("@tinh_tp", strDataFields(3)))
                        MyCommand.Parameters.Add(New SqlParameter("@dienthoai", strDataFields(4)))
                        MyCommand.Parameters.Add(New SqlParameter("@hangsao", IIf(strDataFields(5) = "", DBNull.Value, strDataFields(5))))

                        MyCommand.Parameters.Add(New SqlParameter("@resort", strDataFields(6)))
                        MyCommand.Parameters.Add(New SqlParameter("@tinh_tp1", strDataFields(7)))
                        MyCommand.Parameters.Add(New SqlParameter("@web", strDataFields(8)))
                        MyCommand.Parameters.Add(New SqlParameter("@chuatrangbi", IIf(strDataFields(9) = "", DBNull.Value, strDataFields(9))))
                        MyCommand.ExecuteNonQuery()
                        MyCommand.Dispose()
                        iCount += 1
SQLError:
                    End If
                End If
                i = i + 1
            End If
        End While
        objStreamReader.Close()
        Label1.Text = iCount.ToString + "::" + " Update: " + iUpdate.ToString + " Insert: " + iInsert.ToString
        MyCommand.Dispose()
        myConn.Close()
        Exit Sub
hErr:
        objStreamReader.Close()
        MyCommand.Dispose()
        myConn.Close()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        If Not Page.IsValid Then Exit Sub
        On Error GoTo hErr
        'Open a file for reading
        Dim strName, strCode As String
        Dim iIndex, iCount As Int16
        iCount = 0
        strName = txtName.Text
        Dim FILENAME As String = Server.MapPath("upload\" & strName.ToString)
        'Get a StreamReader class that can be used to read the file
        Dim objStreamReader As StreamReader
        objStreamReader = File.OpenText(FILENAME)

        'Now, read the entire file into a string
        'Dim contents As String = objStreamReader.ReadToEnd()
        Dim sString As String
        Dim strDataFields() As String
        Dim i, intMin, iUpdate, iInsert As Integer
        i = 0 : iUpdate = 0 : iInsert = 0
        Dim sSql As String = Nothing
        Dim myConn As New SqlConnection(ConfigurationManager.AppSettings("cons"))
        Dim MyCommand As SqlCommand
        myConn.Open()
        MyCommand = New SqlCommand("select 1 from daily", myConn)
        While objStreamReader.Peek() <> -1
            sString = objStreamReader.ReadLine()
            sString = remove_SpeKey(sString)
            If Trim(sString) <> "" Then
                If i > 0 Then
                    ' Data 
                    'On Error Resume Next
                    strDataFields = Split(sString, ",")
                    If strDataFields(0) <> "" And strDataFields(1) <> "" Then
                        MyCommand = New SqlCommand(sSql, myConn)
                        sSql = "update daily set bando=@bando where iddaily=@iddaily"
                        iUpdate += 1
                        On Error GoTo SQLError
                        MyCommand.CommandText = sSql
                        MyCommand.Parameters.Add(New SqlParameter("@iddaily", strDataFields(0)))
                        MyCommand.Parameters.Add(New SqlParameter("@bando", strDataFields(1)))
                        MyCommand.ExecuteNonQuery()
                        MyCommand.Dispose()
                        iCount += 1
SQLError:
                    End If
                End If
                i = i + 1
            End If
        End While
        objStreamReader.Close()
        Label1.Text = iCount.ToString + "::" + " Update: " + iUpdate.ToString + " Insert: " + iInsert.ToString
        MyCommand.Dispose()
        myConn.Close()
        Exit Sub
hErr:
        objStreamReader.Close()
        MyCommand.Dispose()
        myConn.Close()
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        If Not Page.IsValid Then Exit Sub
        On Error GoTo hErr
        'Open a file for reading
        Dim strName, strCode As String
        Dim iIndex, iCount As Int16
        iCount = 0
        strName = txtName.Text
        Dim FILENAME As String = Server.MapPath("upload\" & strName.ToString)
        'Get a StreamReader class that can be used to read the file
        Dim objStreamReader As StreamReader
        objStreamReader = File.OpenText(FILENAME)

        'Now, read the entire file into a string
        'Dim contents As String = objStreamReader.ReadToEnd()
        Dim sString As String
        Dim strDataFields() As String
        Dim i, intMin, iUpdate, iInsert As Integer
        i = 0 : iUpdate = 0 : iInsert = 0
        Dim sSql As String = Nothing
        Dim myConn As New SqlConnection(ConfigurationManager.AppSettings("cons"))
        Dim MyCommand As SqlCommand
        myConn.Open()
        MyCommand = New SqlCommand("select 1 from daily", myConn)
        While objStreamReader.Peek() <> -1
            sString = objStreamReader.ReadLine()
            sString = remove_SpeKey(sString)
            If Trim(sString) <> "" Then
                If i > 0 Then
                    ' Data 
                    'On Error Resume Next
                    strDataFields = Split(sString, ";")
                    If strDataFields(0) <> "" And strDataFields(1) <> "" Then
                        MyCommand = New SqlCommand(sSql, myConn)
                        sSql = "update daily set diachis=@diachis, khuvucs=@khuvucs where iddaily=@iddaily"
                        iUpdate += 1
                        On Error GoTo SQLError
                        MyCommand.CommandText = sSql
                        MyCommand.Parameters.Add(New SqlParameter("@iddaily", strDataFields(0)))
                        MyCommand.Parameters.Add(New SqlParameter("@diachis", strDataFields(1)))
                        MyCommand.Parameters.Add(New SqlParameter("@khuvucs", strDataFields(2)))
                        MyCommand.ExecuteNonQuery()
                        MyCommand.Dispose()
                        iCount += 1
SQLError:
                    End If
                End If
                i = i + 1
            End If
        End While
        objStreamReader.Close()
        Label1.Text = iCount.ToString + "::" + " Update: " + iUpdate.ToString + " Insert: " + iInsert.ToString
        MyCommand.Dispose()
        myConn.Close()
        Exit Sub
hErr:
        objStreamReader.Close()
        MyCommand.Dispose()
        myConn.Close()
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click

        If Not Page.IsValid Then Exit Sub

        Try
            'Open a file for reading
            Dim strName As String
            Dim iCount As Int16
            iCount = 0
            strName = txtName.Text
            Dim FILENAME As String = Server.MapPath("upload\" & strName.ToString)
            'Get a StreamReader class that can be used to read the file
            Dim objStreamReader As StreamReader
            objStreamReader = File.OpenText(FILENAME)

            'Now, read the entire file into a string
            'Dim contents As String = objStreamReader.ReadToEnd()
            Dim sString As String
            Dim strDataFields() As String
            Dim i, iUpdate, iInsert As Integer
            i = 0 : iUpdate = 0 : iInsert = 0
            Dim sSql As String = Nothing
            Dim myConn As New SqlConnection(ConfigurationManager.AppSettings("cons"))
            Dim MyCommand As SqlCommand
            myConn.Open()
            MyCommand = New SqlCommand("select 1 from tintuc", myConn)
            While objStreamReader.Peek() <> -1
                sString = objStreamReader.ReadLine()
                sString = remove_SpeKey(sString)
                If Trim(sString) <> "" Then
                    If i > 0 Then
                        ' Data 
                        strDataFields = Split(sString, Chr(9))
                        If strDataFields(0) <> "" Then
                            MyCommand = New SqlCommand(sSql, myConn)
                            sSql = strDataFields(0)
                            iUpdate += 1

                            'Response.Write(iUpdate & ":" & strDataFields(0) & "<br>")

                            MyCommand.CommandText = sSql
                            MyCommand.ExecuteNonQuery()
                            MyCommand.Dispose()
                            iCount += 1

                        End If
                    End If
                    i = i + 1
                End If
            End While
            objStreamReader.Close()
            Label1.Text = iCount.ToString + "::" + " Update: " + iUpdate.ToString + " Insert: " + iInsert.ToString
            MyCommand.Dispose()
            myConn.Close()
            Label3.Text = "Update Successfully"
        Catch ex As Exception
            Label3.Text = "hErr:" & ex.Message
        End Try

    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        If Not Page.IsValid Then Exit Sub
        Try
            'Open a file for reading
            Dim strName As String
            Dim iCount As Int16
            iCount = 0
            strName = txtName.Text
            Dim FILENAME As String = Server.MapPath("upload\" & strName.ToString)
            'Get a StreamReader class that can be used to read the file
            Dim objStreamReader As StreamReader
            objStreamReader = File.OpenText(FILENAME)

            'Now, read the entire file into a string
            'Dim contents As String = objStreamReader.ReadToEnd()
            Dim sString As String
            Dim strDataFields() As String
            Dim i, iUpdate, iInsert As Integer
            i = 0 : iUpdate = 0 : iInsert = 0
            Dim sSql As String = Nothing
            Dim myConn As New SqlConnection(ConfigurationManager.AppSettings("cons"))
            Dim MyCommand As SqlCommand
            myConn.Open()
            MyCommand = New SqlCommand("select 1 from daily", myConn)
            While objStreamReader.Peek() <> -1
                sString = objStreamReader.ReadLine()
                sString = remove_SpeKey(sString)
                If Trim(sString) <> "" Then
                    If i > 0 Then
                        ' Data 
                        strDataFields = Split(sString, Chr(9))
                        If strDataFields(0) <> "" And strDataFields(1) <> "" Then
                            MyCommand = New SqlCommand(sSql, myConn)
                            sSql = "update daily set hinhanh = @tenhinh where iddaily=@iddaily"
                            iUpdate += 1

                            MyCommand.CommandText = sSql
                            MyCommand.Parameters.Add(New SqlParameter("@iddaily", strDataFields(0)))
                            MyCommand.Parameters.Add(New SqlParameter("@tenhinh", Trim(strDataFields(1))))
                            MyCommand.ExecuteNonQuery()
                            MyCommand.Dispose()
                            iCount += 1

                        End If
                    End If
                    i = i + 1
                End If
            End While
            objStreamReader.Close()
            Label1.Text = iCount.ToString + "::" + " Update: " + iUpdate.ToString + " Insert: " + iInsert.ToString
            MyCommand.Dispose()
            myConn.Close()
            Label3.Text = "Update Successfully"
        Catch ex As Exception
            Label3.Text = "hErr:" & ex.Message
        End Try
    End Sub

    Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
        If Not Page.IsValid Then Exit Sub

        Try
            'Open a file for reading
            Dim strName As String
            Dim iCount As Int16
            iCount = 0
            strName = txtName.Text
            Dim FILENAME As String = Server.MapPath("upload\" & strName.ToString)
            'Get a StreamReader class that can be used to read the file
            Dim objStreamReader As StreamReader
            objStreamReader = File.OpenText(FILENAME)

            'Now, read the entire file into a string
            'Dim contents As String = objStreamReader.ReadToEnd()
            Dim sString As String
            Dim strDataFields() As String
            Dim i, iUpdate, iInsert As Integer
            i = 0 : iUpdate = 0 : iInsert = 0
            Dim sSql As String = Nothing
            Dim myConn As New SqlConnection(ConfigurationManager.AppSettings("cons"))
            Dim MyCommand As SqlCommand
            myConn.Open()
            MyCommand = New SqlCommand("select 1 from daily", myConn)
            While objStreamReader.Peek() <> -1
                sString = objStreamReader.ReadLine()
                sString = remove_SpeKey(sString)
                If Trim(sString) <> "" Then
                    If i > 0 Then
                        ' Data 
                        strDataFields = Split(sString, Chr(9))
                        If strDataFields(0) <> "" And strDataFields(1) <> "" Then
                            MyCommand = New SqlCommand(sSql, myConn)
                            sSql = "update daily set bando = @tenhinh where dienthoai like '%" & Trim(strDataFields(0)) & "%'"
                            iUpdate += 1

                            MyCommand.CommandText = sSql
                            MyCommand.Parameters.Add(New SqlParameter("@tenhinh", Trim(strDataFields(1))))
                            MyCommand.ExecuteNonQuery()
                            MyCommand.Dispose()
                            iCount += 1

                        End If
                    End If
                    i = i + 1
                End If
            End While
            objStreamReader.Close()
            Label1.Text = iCount.ToString + "::" + " Update: " + iUpdate.ToString + " Insert: " + iInsert.ToString
            MyCommand.Dispose()
            myConn.Close()
            Label3.Text = "Update Successfully"
        Catch ex As Exception
            Label3.Text = "hErr:" & ex.Message
        End Try

    End Sub

    Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click
        If Not Page.IsValid Then Exit Sub
        Try
            'Open a file for reading
            Dim strName As String
            Dim iCount As Int16
            iCount = 0
            strName = txtName.Text
            Dim FILENAME As String = Server.MapPath("upload\" & strName.ToString)
            'Get a StreamReader class that can be used to read the file
            Dim objStreamReader As StreamReader
            objStreamReader = File.OpenText(FILENAME)

            'Now, read the entire file into a string
            'Dim contents As String = objStreamReader.ReadToEnd()
            Dim sString As String
            Dim strDataFields() As String
            Dim i, iUpdate, iInsert As Integer
            i = 0 : iUpdate = 0 : iInsert = 0
            Dim sSql As String = Nothing
            Dim myConn As New SqlConnection(ConfigurationManager.AppSettings("cons"))
            Dim MyCommand As SqlCommand
            myConn.Open()
            MyCommand = New SqlCommand("select * from sanpham", myConn)
            While objStreamReader.Peek() <> -1
                sString = objStreamReader.ReadLine()
                sString = remove_SpeKey(sString)
                If Trim(sString) <> "" Then
                    If i > 0 Then
                        ' Data 
                        strDataFields = Split(sString, Chr(9))
                        If strDataFields(1) <> "" And strDataFields(2) <> "" Then
                            MyCommand = New SqlCommand(sSql, myConn)
                            sSql = "update sanpham set hinhnho1 = @hinhnho1, hinhnho2 = @hinhnho2 where idsanpham=@idsanpham"
                            iUpdate += 1

                            MyCommand.CommandText = sSql
                            MyCommand.Parameters.Add(New SqlParameter("@idsanpham", strDataFields(1)))
                            MyCommand.Parameters.Add(New SqlParameter("@hinhnho1", Trim(strDataFields(2))))
                            MyCommand.Parameters.Add(New SqlParameter("@hinhnho2", Trim(strDataFields(3))))
                            MyCommand.ExecuteNonQuery()
                            MyCommand.Dispose()
                            iCount += 1

                        End If
                    End If
                    i = i + 1
                End If
            End While
            objStreamReader.Close()
            Label1.Text = iCount.ToString + "::" + " Update: " + iUpdate.ToString + " Insert: " + iInsert.ToString
            MyCommand.Dispose()
            myConn.Close()
            Label3.Text = "Update Successfully"
        Catch ex As Exception
            Label3.Text = "hErr:" & ex.Message
        End Try
    End Sub

End Class